package com.lele.dao;

import com.lele.pojo.Tang;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BaseDaoImpl implements BaseDao{
    // 增
    public int add(Tang tang) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        int flag = 0;
        conn = JDBCUtilDao.getConnection();
        String sql = "insert into tang(name,sex,age) values (?,?,?)";
        st = conn.prepareStatement(sql);
        st.setString(1,tang.getName());
        st.setString(2,tang.getSex());
        st.setInt(3, tang.getAge());
        flag = st.executeUpdate();
        JDBCUtilDao.release(st,re);
        return flag;
    }
    // 删
    public int delete(int id) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        int flag = 0;
        conn = JDBCUtilDao.getConnection();
        String sql = "delete from tang where id=?";
        st = conn.prepareStatement(sql);
        st.setInt(1,id);
        flag = st.executeUpdate();
        JDBCUtilDao.release(st,re);
        return flag;
    }
    // 改
    public int update(Tang tang) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        int flag = 0;
        conn = JDBCUtilDao.getConnection();
        String sql = "update tang set name=?,sex=?,age=? where id=?";
        st = conn.prepareStatement(sql);
        st.setString(1,tang.getName());
        st.setString(2,tang.getSex());
        st.setInt(3,tang.getAge());
        st.setInt(4,tang.getId());
        flag = st.executeUpdate();
        JDBCUtilDao.release(st,re);
        return flag;
    }
    // 查询所有
    public List<Tang> selectAll() throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        ArrayList<Tang> arrayList = new ArrayList<Tang>();
        conn = JDBCUtilDao.getConnection();
        String sql = "select * from tang";
        st = conn.prepareStatement(sql);
        re = st.executeQuery();
        while (re.next()){
            Tang tang = new Tang();
            tang.setId(re.getInt("id"));
            tang.setName(re.getString("name"));
            tang.setSex(re.getString("sex"));
            tang.setAge(re.getInt("age"));
            arrayList.add(tang);
        }
        JDBCUtilDao.release(st,re);
        return arrayList;
    }

    // 查询单个
    public Tang selectOne(int id) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        Tang tang = new Tang();
        conn = JDBCUtilDao.getConnection();
        String sql = "select * from tang where id=?";
        st = conn.prepareStatement(sql);
        st.setInt(1,id);
        re = st.executeQuery();
        if (re.next()){
            tang.setId(re.getInt("id"));
            tang.setName(re.getString("name"));
            tang.setSex(re.getString("sex"));
            tang.setAge(re.getInt("age"));
        }
        JDBCUtilDao.release(st,re);
        return tang;
    }

    // 模糊查询
    public List<Tang> selectLike(String name) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        ArrayList<Tang> arrayList = new ArrayList<Tang>();
        conn = JDBCUtilDao.getConnection();
        String sql = "select * from tang where name like ?";
        st = conn.prepareStatement(sql);
        st.setString(1, "%"+name+"%");
        re = st.executeQuery();
        while (re.next()){
            Tang tang = new Tang();
            tang.setId(re.getInt("id"));
            tang.setName(re.getString("name"));
            tang.setSex(re.getString("sex"));
            tang.setAge(re.getInt("age"));
            arrayList.add(tang);
        }
        JDBCUtilDao.release(st,re);
        return arrayList;
    }

    // 分页查询
    public List<Tang> selectPaging(int pageNo, int a) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        ArrayList<Tang> arrayList = new ArrayList<Tang>();
        conn = JDBCUtilDao.getConnection();
        String sql = "select * from tang limit ?,?";
        st = conn.prepareStatement(sql);
        st.setInt(1, (pageNo-1)*a);
        st.setInt(2,a);
        re = st.executeQuery();
        while (re.next()){
            Tang tang = new Tang();
            tang.setId(re.getInt("id"));
            tang.setName(re.getString("name"));
            tang.setSex(re.getString("sex"));
            tang.setAge(re.getInt("age"));
            arrayList.add(tang);
        }
        JDBCUtilDao.release(st,re);
        return arrayList;
    }

    // 查询总条数
    public int selectSum() throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        int sum = 0;
        conn = JDBCUtilDao.getConnection();
        String sql = "select count(*) sum from tang";
        st = conn.prepareStatement(sql);
        re = st.executeQuery();
        if (re.next()){
            sum = re.getInt("sum");
        }
        JDBCUtilDao.release(st,re);
        return sum;
    }
}
